package com.tomdxz.daoimpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.tomdxz.dao.UserDao;
import com.tomdxz.model.User;
import com.tomdxz.util.JDBCutil;

public class UserDaoImpl implements UserDao{

	@Override
	public List<User> getUser(int page, int rows) {
		List<User> users = new ArrayList<User>();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT c_user.uid,c_user.uaccount,c_user.upsw");
		sql.append(" ,c_user.uname,c_user.rid,c_role.rname FROM c_user");
		sql.append(" INNER JOIN c_role ON c_role.rid = c_user.rid");
		sql.append(" LIMIT ?,?");
		Connection con = JDBCutil.getConnection();
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setInt(1, (page-1)*rows);
			pst.setInt(2, rows);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				User u = new User();
				u.setuId(rs.getInt("uid"));
				u.setuAccount(rs.getString("uaccount"));
				u.setuPsw(rs.getString("upsw"));
				u.setuName(rs.getString("uname"));
				u.setRid(rs.getInt("rid"));
				u.setrName(rs.getString("rname"));
				users.add(u);
			}
			JDBCutil.closeJDBC(pst, con);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}		
		return users;
	}

	@Override
	public int getCountUser() {
		int total = 0;
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT COUNT(uid) AS total FROM c_user");
		Connection con = JDBCutil.getConnection();
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				total = rs.getInt("total");
			}
			JDBCutil.closeJDBC(pst, con);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}				
		return total;
	}

	@Override
	public int insertUser(String uaccount, String upsw, String uname, int rid) {
		int flag = 0;
		StringBuffer sql = new StringBuffer();
		sql.append("INSERT INTO c_user(uaccount,upsw,uname,rid)");
		sql.append(" VALUES(?,?,?,?)");
		Connection con = JDBCutil.getConnection();
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, uaccount);
			pst.setString(2, upsw);
			pst.setString(3, uname);
			pst.setInt(4, rid);
			flag = pst.executeUpdate();			
			JDBCutil.closeJDBC(pst, con);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}		
		return flag;
	}

	@Override
	public int updateUserByUid(String uaccount, String upsw, String uname, int rid, int uId) {
		int flag = 0;
		StringBuffer sql = new StringBuffer();
		sql.append("UPDATE c_user");
		sql.append(" SET uaccount=?,upsw=?,uname=?,rid=?");
		sql.append(" WHERE uid=?");
		Connection con = JDBCutil.getConnection();
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, uaccount);
			pst.setString(2, upsw);
			pst.setString(3, uname);
			pst.setInt(4, rid);
			pst.setInt(5, uId);
			flag = pst.executeUpdate();
			JDBCutil.closeJDBC(pst, con);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
		
		return flag;
	}

}
